let mysql = require('mysql')
let config = require('./config/config')
const { mysqlConfig } = config
let connection = mysql.createConnection({
    host: mysqlConfig.host,
    user: mysqlConfig.user,
    password: mysqlConfig.password
})

// 封装执行sql语句的函数
function querySql (sqlstr) {
    return new Promise(function (resolve, reject) {
        connection.query(sqlstr, function (err, result) {
            if (err) {
                reject({ err })
                return
            }
            console.log('sql ok')
            resolve({ result })
        })
    }).then(data => data).catch(err => err)
}

async function createDatabase () {
    connection.connect()
    // 1.创建一个数据库
    let data = await querySql('create database esql')
    console.log(data)
    if (data.err) {
        data = await querySql('drop database esql')
        if (data.err) return
        data = await querySql('create database esql')
        if (data.err) return
    }
    // 2.切换到创建的数据库
    data = await querySql('use esql')
    // 3.创建表
    data = await querySql(`
    create table users(
        user_id int primary key not null auto_increment,
        account char(32) not null,
        pswd char(32) not null,
        type_id int,
        username char(32),
        createTime timestamp
    )engine=innodb default charset=utf8;
    `)
    // 插入数据 超级管理员账号
    data = await querySql("insert into users(account,pswd,type_id,username,createTime) values('admin','admin',1,'超级管理员',now())")
    // 创建菜单表
    data = await querySql(`
        create table menu(
            id int primary key auto_increment,
            createTime timestamp,
            icon char(100),
            menu_id int,
            m_id char(10),
            parent_m_id char(10),
            path char(100),
            title char(10)
        )engine=innodb default charset=utf8;
    `)
    data = await querySql("insert into menu(menu_id,m_id,parent_m_id,path,title,createTime) values(1,'/power',null,'/power','系统设置',now())")
    data = await querySql("insert into menu(menu_id,m_id,parent_m_id,path,title,createTime) values(2,'/user','/power','/user','用户管理',now())")
    data = await querySql("insert into menu(menu_id,m_id,parent_m_id,path,title,createTime) values(3,'/menu',null,'/menu','菜单管理',now())")

    // 创建权限表
    data = await querySql(`
    create table power(
        id int primary key auto_increment,
        type_id int,
        name char(10),
        menu_id char(10),
        createTime timestamp
    )engine=innodb default charset=utf8;
`)
    data = await querySql("insert into power(menu_id,name,type_id,createTime) values('1,2,3','管理员',1,now())")
    console.log('数据库创建完成')
    connection.destroy();// 关闭数据库
}

createDatabase()